/*
 *接口编写处...
*如果接口需要做Action的权限验证，请在Action上使用属性
*如: [ApiActionPermission("new_overtime",Enums.ActionPermissionOptions.Search)]
 */
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.Entity.DomainModels;
using VOL.Test.IServices;
using OfficeOpenXml;
using System.Data;
using System.IO;
using System.Reflection;
using Sys = System;
using VOL.Core.DBManager;
using VOL.Core.ManageUser;

namespace VOL.Test.Controllers
{
    public partial class new_overtimeController
    {
        private readonly Inew_overtimeService _service;//访问业务代码
        private readonly IHttpContextAccessor _httpContextAccessor;

        [ActivatorUtilitiesConstructor]
        public new_overtimeController(
            Inew_overtimeService service,
            IHttpContextAccessor httpContextAccessor
        )
        : base(service)
        {
            _service = service;
            _httpContextAccessor = httpContextAccessor;
        }

        [HttpPost, Route("ExportByTemptate")]
        public IActionResult ExportByTemptate(PageDataOptions pageData)
        {
            List<new_overtime> list = _service.GetPageData(pageData).rows;
            byte[] bytes = null;
            //模板上传路径
            string filePath = @"D:\Upload\测试加班申请明细导出模板.xlsx";
            //导出记录路径
            string tempPath = @"D:\Download\";
            //导出文件名：模板名称 + 时间戳 + 工号(名字)
            tempPath = tempPath + Path.GetFileName(filePath).Replace(".xlsx", "") + "_" + 
                DateTime.Now.ToString("yyMMddhhmmss") + "_" + UserContext.Current.UserInfo.UserName + "(" + UserContext.Current.UserInfo.UserTrueName + ").xlsx";
            Sys.IO.File.Copy(filePath, tempPath, true);

            List<string> columnNames = new List<string>();  

            using (var package = new ExcelPackage(new FileInfo(filePath)))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[2]; // 获取第2个工作表
                int colCount = worksheet.Dimension.End.Column; // 获取总列数

                for (int col = 1; col <= colCount; col++)
                {
                    string columnName = worksheet.Cells[1, col].Text;
                    columnNames.Add(columnName);  //获取模板中维护的需要导出的列名
                }

                //记录导出列中文名和实际字段名 的对应关系
                Dictionary<string, object> dic = new Dictionary<string, object>();
                string ColumnCNName = string.Empty;
                var dapper = DBServerProvider.GetSqlDapper();
                DataTable dt = new DataTable();
                foreach (PropertyInfo property in typeof(new_overtime).GetProperties())
                {
                    
                    string sql = @$"select * from Sys_TableColumn where ColumnName = 
                                        '{property.Name}' and TableName = 'new_overtime'";
                    var Sys_TableColumn = dapper.QueryFirst<Sys_TableColumn>(sql, null);
                    if(Sys_TableColumn != null)
                    {
                        ColumnCNName = Sys_TableColumn.ColumnCnName;
                    }
                    if (columnNames.Contains(ColumnCNName))
                    {
                        dt.Columns.Add(property.Name);
                        dic.Add(property.Name, ColumnCNName);   //临时存放到Datatable中
                    }
                    
                }

                // 开始从list中读取数据并写入DataTable
                for (int i = 0; i < list.Count; i++)
                {
                    DataRow row = dt.NewRow();
                    foreach (DataColumn column in dt.Columns)
                    {
                        object? value = list[i].GetType().GetProperty(column.ColumnName)?.GetValue(list[i], null);
                        row[column.ColumnName] = value ?? DBNull.Value;
                    }
                    dt.Rows.Add(row);
                }

                // 将Datatable中的数据写入到Excel中
                // 写入列名
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = dic[dt.Columns[i].ColumnName];
                }

                // 写入数据行
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1].Value = dt.Rows[i][j];
                    }
                }

                // 保存更改
                package.Save();
                bytes = package.GetAsByteArray();
            }

            return File(bytes, Sys.Net.Mime.MediaTypeNames.Application.Octet, "123.xlsx");

        }

    }
}
